package My

import (
	"fmt"
	"db2s/ETL"
	"db2s/global"
	"strings"
)

func (my TableInfoMeta) PackageName(s global.TablesMetaInfoInput) (result global.ObjectNameReturnResult, err error) {
	var f1 any
	var event = "[MySQLPackageName]"
	result.BaseResult.Sql = fmt.Sprintf("select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where  ROUTINE_TYPE = 'PACKAGE' AND ROUTINE_SCHEMA ='%v' %v",
		s.TableInfo.Schema, func() string {
			switch {
			case s.TableInfo.Table == "*":
				return ""
			case strings.Contains(s.TableInfo.Table, "%"):
				return fmt.Sprintf(" and ROUTINE_NAME like '%%%v%%'", s.TableInfo.Table)
			default:
				return fmt.Sprintf(" and ROUTINE_NAME = '%v'", s.TableInfo.Table)
			}
		}())
	if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: result.BaseResult.Sql,
		Expect:       ETL.StringGarth,
	}); err != nil || f1 == nil {
		return
	}
	result.Result = f1.([]string)
	return
}

func (my TableInfoMeta) PackageExist(s global.TablesMetaInfoInput) (result global.ObjectExistReturnResult, err error) {
	var f1 any
	var event = "[MySQLPackageExist]"
	result.BaseResult.Sql = fmt.Sprintf("select count(1) as `sum` from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PACKAGE' and ROUTINE_SCHEMA = '%v' and ROUTINE_NAME = '%v'", s.TableInfo.Schema, s.TableInfo.Table)
	if result.BaseResult.TimeOut, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: result.BaseResult.Sql,
		Expect:       ETL.Map,
	}); err != nil || f1 == nil {
		return
	}
	if fmt.Sprintf("%v", f1.(map[string]any)["sum"]) == "1" {
		result.Result = true
	}
	return
}

func packagePrintInfo(x *global.PackageResultDefinition) (res string) {
	/*
		-- 包规范
		CREATE OR REPLACE PACKAGE my_package AS
			g_message VARCHAR2(100);
			PROCEDURE my_procedure(param1 IN NUMBER);
			FUNCTION my_function(param1 IN NUMBER) RETURN VARCHAR2;
		END my_package;
		/

		-- 包体
		CREATE OR REPLACE PACKAGE BODY my_package AS
			PROCEDURE my_procedure(param1 IN NUMBER) IS
			BEGIN
				g_message := 'The number is: ' || param1;
				DBMS_OUTPUT.PUT_LINE(g_message);
			END my_procedure;

			FUNCTION my_function(param1 IN NUMBER) RETURN VARCHAR2 IS
			BEGIN
				RETURN 'The function received: ' || param1;
			END my_function;
		END my_package;
		/
	*/
	return fmt.Sprintf("CREATE OR REPLACE %v\n"+
		"CREATE OR REPLACE %v\n"+
		"/", x.HeadText, x.BodyText,
	)
}
func packageDefinitionConvert(x global.PackageResultDefinition) (convert global.PackageDefinitionConvertS) {
	convert = global.PackageDefinitionConvertS{
		Schema:      x.Schema,
		Name:        x.Name,
		Permissions: x.Permissions,
		HeadText:    x.HeadText,
		BodyText:    x.BodyText,
		Comment:     x.Comment,
		PInput:      x.PInput,
		POutput:     x.POutput,
		Type:        x.Type,
		PrintInfo:   x.PrintInfo,
	}
	return
}
func getPackageBody(s global.SchemaObjectInput) (res string, err error) {
	var (
		f1    any
		event = "[MySQLPackageBody]"
		r     []string
	)
	sqlStr := fmt.Sprintf("SELECT\n"+
		"\tROUTINE_SCHEMA AS `schema`,\n"+
		"\tROUTINE_NAME AS `name`,\n"+
		"\tDEFINER  AS `permissions`,\n  "+
		"\tROUTINE_DEFINITION AS `body`,\n"+
		"\tROUTINE_COMMENT AS `comment`\n"+
		"FROM\n"+
		"\tinformation_schema.ROUTINES \n"+
		"WHERE 1 = 1\n"+
		"\tAND ROUTINE_TYPE = 'PACKAGE BODY' \n"+
		"\tAND ROUTINE_SCHEMA = '%v' \n"+
		"\tAND ROUTINE_NAME = '%v'", s.Schema, s.Table)
	if _, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: sqlStr,
		Expect:       ETL.SMap,
	}); err != nil || f1 == nil {
		return
	}
	for _, v := range f1.([]map[string]any) {
		r = append(r, fmt.Sprintf("%v", v["text"]))
	}
	res = strings.Join(r, "")
	return
}
func getPackageHead(s global.SchemaObjectInput) (res string, err error) {
	var (
		f1    any
		event = "[MySQLPackageHead]"
		r     []string
	)
	sqlStr := fmt.Sprintf("SELECT\n"+
		"\tROUTINE_SCHEMA AS `schema`,\n"+
		"\tROUTINE_NAME AS `name`,\n"+
		"\tDEFINER  AS `permissions`,\n  "+
		"\tROUTINE_DEFINITION AS `body`,\n"+
		"\tROUTINE_COMMENT AS `comment`\n"+
		"FROM\n"+
		"\tinformation_schema.ROUTINES \n"+
		"WHERE 1 = 1\n"+
		"\tAND ROUTINE_TYPE = 'PACKAGE' \n"+
		"\tAND ROUTINE_SCHEMA = '%v' \n"+
		"\tAND ROUTINE_NAME = '%v'", s.Schema, s.Table)
	if _, f1, err = ExecSelectSql(ExecSqlInputS{
		Ss:           s,
		Event:        event,
		SelectSqlStr: sqlStr,
		Expect:       ETL.SMap,
	}); err != nil || f1 == nil {
		return
	}
	for _, v := range f1.([]map[string]any) {
		r = append(r, fmt.Sprintf("%v", v["text"]))
	}
	res = strings.Join(r, "")
	return
}
func (my SchemaObjectMeta) Package(s global.SchemaObjectInput) (res global.SchemaObjectReturnResult, err error) {
	var (
		x global.PackageResultDefinition
	)
	x.Schema = s.Schema
	x.Name = s.Table
	if x.HeadText, err = getPackageHead(s); err != nil {
		return
	}
	if x.BodyText, err = getPackageBody(s); err != nil {
		return
	}
	x.Type = "package"
	x.PrintInfo = packagePrintInfo(&x)
	res.PackageResult.Definition = x
	res.PackageResult.Convert = packageDefinitionConvert(x)
	return
}
func packagePrintInfoConvert(s string) (res string) {
	res = strings.TrimSpace(s)
	if strings.HasSuffix(res, "/") {
		res = strings.TrimSpace(res[:len(res)-1])
	}
	if strings.HasSuffix(res, ";") {
		res = strings.TrimSpace(res[:len(res)-1])
	}
	return
}
func createPackage(s global.SchemaObjectInput) (res global.ObjectFixSqlS, err error) {
	res.CreateSql = append(res.CreateSql, fmt.Sprintf("use %v; \n"+
		"delimiter /\n"+
		"%v \n"+
		"/ \n"+
		"delimiter ;",
		s.Schema,
		packagePrintInfoConvert(s.ObjectDefinition.PackageResult.Convert.PrintInfo)))
	return
}
